-- =============================================
-- This stored procedure deletes currently active content record
-- and creates a working version of old content record.
--
-- Modifications
-- 09/08/2003    E.Tatsui   Created
-- =============================================


CREATE       PROCEDURE amsp_CMRetrieveContentAsNew
  @InContentID numeric,
  @InContactID numeric,
  @OutWorkingContentID numeric OUTPUT
AS
BEGIN
  DECLARE
    @CurrentContentID numeric,
    @CurrentContentStatus char(1),
    @OriginalContentID numeric,
    @CurrentNavMenuID numeric
    
  SELECT @CurrentContentID = b.ContentID,
         @CurrentContentStatus = b.WorkflowStatusCode,
         @OriginalContentID = b.OriginalContentID,
         @CurrentNavMenuID = b.NavMenuID
    FROM Content a, vCurrent_Content b
   WHERE a.OriginalContentID = b.OriginalContentID
     AND a.ContentID = @InContentID

  IF @CurrentNavMenuID IS NULL
     SELECT @CurrentNavMenuID = NavMenuID
       FROM Content
      WHERE ContentID = @InContentID

  IF @CurrentContentStatus IN ('A','W')
    EXEC amsp_CMDeleteContent @CurrentContentID, @InContactID, 'Y', NULL

  EXEC amsp_CMCopyContentRow @InContentID, @InContactID, @OutWorkingContentID OUTPUT
  
  -- Store the previous current content id as previous id.
  -- Also update NavMenuID to current nav menu id, in case it has been changed by moving.
  UPDATE Content
     SET PreviousContentID = (SELECT Max(ContentID)
                                FROM Content
                               WHERE OriginalContentID = @OriginalContentID
                                 AND ContentID <> @OutWorkingContentID),
         NavMenuID = @CurrentNavMenuID,
         SortOrder = (SELECT IsNull(Max(SortOrder),0) + 1
                        FROM Content
                       WHERE NavMenuID = @CurrentNavMenuID)
   WHERE ContentID = @OutWorkingContentID
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMRetrieveContentAsNew] TO [IMIS]
GO